package service;

import constant.ConstDataBase;
import model.ViewForGrid;
import model.ViewForRelation;
import pond.common.JSON;
import pond.common.STRING;
import pond.common.f.Tuple;
import pond.db.DB;
import java.io.File;
import java.util.*;

/**
 * Created by sky_wang on 2017/2/7.
 */
public class DataBaseService {

    private List<String> dataTableList = new ArrayList<>();
    private List<String> relationTableList = new ArrayList<>();
    private Map<String,List<Tuple<String,Integer>>> databaseMap = new HashMap<>();
    private Map<String, Map<String, String>> relationGroupMap = new HashMap<>();

    private File dataDir;
    private DB   db;

    public DB getDB(){
        return db;
    }
    public File getDataDir(){
        return dataDir;
    }
    public  DataBaseService(DB db, File path, Map<String,String> tableMap){

        if (db != null)
        {
            this.db = db;
        }

        if (path.exists())
        {
            this.dataDir = path;
        }

        Map<String, Map<String, Integer>> dbMap = db.getDbStructures();
        for (Map.Entry<String, Map<String, Integer>> entry : dbMap.entrySet()) {
            String tableName = entry.getKey();
            if (tableName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                dataTableList.add(tableName);
            }
            if (tableName.startsWith(ConstDataBase.RELATION_TABLE_HEAD_FLAG)) {
                relationTableList.add(tableName);
            }
            Map<String, Integer> columnMap = entry.getValue();
            List<Tuple<String,Integer>> columnList = new ArrayList<>();
            for (Map.Entry<String, Integer> subentry : columnMap.entrySet()) {
                columnList.add(Tuple.t2( subentry.getKey(), subentry.getValue()));
            }
            databaseMap.put(tableName, columnList);
        }
        /* 检测是否存在表，没有则创建表 */
        for (Map.Entry<String, String> table : tableMap.entrySet()) {
            createTable(table.getKey(),table.getValue());
        }

        /* 检测是否关系配置数据，存在则取出配置，不存在新建*/
        RelationService relationService = new RelationService(this.db);
        ViewForRelation vfr = relationService.get("0");
        if (vfr == null)
        {
            setRelationGroupMap();
            relationService.add(new ViewForRelation(){
                {
                    set("vid","0");
                    set("relation_data_default", JSON.stringify(relationGroupMap));
                    set("relation_data_config",  JSON.stringify(relationGroupMap));
                }
            });
        }
        else
        {
            if (STRING.notBlank(vfr.get("relation_data_config").toString()))
            {
                updateRelationGroupMap(vfr.get("relation_data_config").toString());
            }
            else
            {
                updateRelationGroupMap(vfr.get("relation_data_default").toString());
            }
        }

    }


    public  void setRelationGroupMap(){
        //{ key :r_ value :  [{name : id} {name :id }]}
        for (Map.Entry<String, List<Tuple<String,Integer>>> entry : databaseMap.entrySet()) {
            String tableName = entry.getKey();
            System.out.println("getMapOfRelationshipTable tablename:" + tableName);
            if (tableName.startsWith(ConstDataBase.RELATION_TABLE_HEAD_FLAG)) {
                List<Tuple<String,Integer>> columnList = entry.getValue();
                Map<String, String> mapDataTable = new HashMap<>();
                for (Tuple<String,Integer> item : columnList) {
                    if (item._a.indexOf("_id") >= 0) {
                        mapDataTable.put(getDataTableName(item._a), item._a);
                    } else {
                        mapDataTable.put(ConstDataBase.DATA_TABLE_HEAD_FLAG + item._a, item._a);
                    }
                }
                relationGroupMap.put(tableName, mapDataTable);
            }
            if (tableName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                List<Tuple<String,Integer>> columnList = entry.getValue();
                for (Tuple<String,Integer> item : columnList) {
                    if (item._a.indexOf("_id") >= 0) {
                        //String foreginTableName = DATA_TABLE_HEAD_FLAG + subentry.getKey().replace("_id", "");
                        // 找 截断_id 的字符串结尾的表名
                        String foreginTableName = item._a.replace("_id", "");
                        for (String dataTableName : dataTableList) {
                            if (dataTableName.endsWith(foreginTableName)){
                                foreginTableName = dataTableName;
                            };
                        }

                        if (databaseMap.containsKey(foreginTableName)) {
                            //-> tableName
                            //{ key :t_  + t_  :  [{t_ : id} {t_ :id }]}
                            //tableName +foreginTableName
                            Map<String, String> mapDataTable = new HashMap<>();
                            mapDataTable.put(tableName, item._a);
                            mapDataTable.put(foreginTableName, "id");
                            relationGroupMap.put(tableName + ConstDataBase.V_AND + foreginTableName, mapDataTable);
                            System.out.println("map:" + tableName + ";" + foreginTableName);
                        }
                    }
                }
            }
        }
    }
    public void updateRelationGroupMap(String json){
        if (STRING.isBlank(json))
        {
            return;
        }
        if (relationGroupMap != null && relationGroupMap.size() >0)
        {
            relationGroupMap.clear();
        }
        relationGroupMap.putAll(JSON.parse(json));
    }

    public  void createTable(String name, String sql){
        if (databaseMap.containsKey(name))
        {
            return;
        }
        else
        {
            this.db.post(t->t.exec(sql));
        }
    }



    private boolean isExist(String tableName) {
        for (String item : dataTableList) {
            if (item.equals(tableName)) {
                return true;
            }
        }
        return false;
    }

    private String getDataTableName(String keyName) {
        int loc = keyName.indexOf("_id");//首先获取字符的位置
        if (loc < 0) {
            return null;
        }
        String dataTableName = keyName.substring(0, loc);//再对字符串进行截取，获得想要得到的字符串
        dataTableName = (ConstDataBase.DATA_TABLE_HEAD_FLAG + dataTableName);
        if (isExist(dataTableName)) {
            return dataTableName;
        } else {
            return null;
        }
    }

    public String getValueByKeyFromJson(String key, String json) {
        Map<String, Object> viewMap = JSON.parse(json);
        for (Map.Entry<String, Object> subentry : viewMap.entrySet()) {
            System.out.println(key + subentry.getKey());
            if (key.equals(subentry.getKey())) {
                return subentry.getValue().toString();
            } else {
                continue;
            }
        }
        return null;
    }

    /* {"key":"t_user___name",
        "value":{"key":"in","value":["wangfeng","gufei"]}},*/
    private String getWhereOfViewSql(String json) {
        String whereStr = " where 1=1 ";
        String queryIdsStr = getValueByKeyFromJson("query_ids", json);
        if (STRING.isBlank(queryIdsStr))
        {
            return whereStr;
        }
        List<Map<String, Object>> listQueryOptions = JSON.parseArray(queryIdsStr);

        for (Map<String, Object> item : listQueryOptions) {
            String queryOptItem = (String) item.get("key");
            Map<String, Object> subQueryMap = (Map<String, Object>) item.get("value");
            String queryKey = (String) subQueryMap.get("key");
            if ("in".equals(queryKey)) {
                List<Object> subQueryValuesList = (List<Object>) subQueryMap.get("value");
                String[] queryArr = queryOptItem.toString().split(ConstDataBase.V_AND);
                if (queryArr.length ==2) {
                    whereStr += " and " + queryArr[queryArr.length -1] +"." +queryArr[0] + " in " + subQueryValuesList.toString().replace("[", "(").replace(", ", "', '").replace("]", ")");
                }
                else if (queryArr.length >2)
                {
                    whereStr += " and " + queryArr[queryArr.length -1] +"." +queryOptItem.toString()+ " in " + subQueryValuesList.toString().replace("[", "(").replace(", ", "', '").replace("]", ")");
                }
                else
                {

                }
            }
        }
        return whereStr;
    }

    //show{'t_','vr_'}
    private List<Tuple<String, String>> getListforQueryOptions(String json) {
        List<Tuple<String, String>> listRet = new ArrayList<>();
        List<String> listQueryOptions = JSON.parseArray(getValueByKeyFromJson("show_tables", json));

        List<String>newList = new  ArrayList<>();
        Set set = new  HashSet();
        for (String item: listQueryOptions) {
            if(set.add(item)){
                newList.add(item);
            }
        }
        for (String item : newList) {
            if (item.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                List<String> listColsOfDataTable = getColumnsListOfDataTable(item);
                for (String subItem : listColsOfDataTable) {
                    listRet.add(Tuple.pair(subItem, item));
                }
            } else if (item.startsWith(ConstDataBase.VIEW_HRAD_FLAG)) {
                List<Tuple<String, String>> listColsOfView = getColumnsListOfView(item);
                listRet.addAll(listColsOfView);
            } else {
                System.out.println("DataBaseService getListforQueryOptions error parm:" + item);
                continue;
            }
        }
        return listRet;
    }

    public List<String> getAllConsistOfTableOfViewByVid(String viewId) {
        ViewForGrid vfg = getViewForGridByVid(viewId);
        if (null != vfg) {
            return JSON.parseArray(vfg.get("relational_data_tables"));
        } else {
            return null;
        }
    }

    private String getAllConsistOfTablesStrFromView(String json) {
        List<String> listRet = new ArrayList<>();
        List<String> listQueryOptions = JSON.parseArray(getValueByKeyFromJson("show_tables", json));
        List<String> newList = new  ArrayList<>();
        Set set = new  HashSet();
        for (String item: listQueryOptions) {
            if(set.add(item)){
                newList.add(item);
            }
        }

        for (String item : newList) {
            if (item.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG) && (!listRet.contains(item))) {
                listRet.add(item);
            } else if (item.startsWith(ConstDataBase.VIEW_HRAD_FLAG)) {
                List<String> listTablesOfView = getAllConsistOfTableOfViewByVid(item);
                for (String subItem : listTablesOfView) {
                    if (!listRet.contains(item)) {
                        listRet.add(subItem);
                    }
                }

            } else {
                System.out.println("DataBaseService getAllConsistOfTablesFromView error parm:" + item);
                continue;
            }
        }
        return JSON.stringify(listRet);
    }

    public String getSelectOptionsForSqlQuery(String json, String vid) {
        List<Tuple<String, String>> list = getListforQueryOptions(json);

        List listForTableOfView = new ArrayList();
        String tableOfView  = getValueByKeyFromJson("table_of_view", json);
        if (STRING.notBlank(tableOfView))
        {
            listForTableOfView = JSON.parseArray(tableOfView);
        }
        String selectOptionsSqlStr = "";
        for (Tuple<String, String> item : list) {
            if (item._b.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                String newViewVID ="";
                for (Object subItem : listForTableOfView)
                {
                    Map<String,String> map =  (Map<String,String>)subItem;
                    if (item._b.equals(map.get("key"))) {
                        newViewVID = map.get("value")+ ConstDataBase.V_AND;
                    }
                }
                selectOptionsSqlStr += item._b + "." + item._a + " as " + item._a + ConstDataBase.V_AND + item._b + ConstDataBase.V_AND + newViewVID + vid + ",";
            } else if (item._b.startsWith(ConstDataBase.VIEW_HRAD_FLAG)) {
                String[] subCols = item._a.split(ConstDataBase.V_AND);
                if (subCols.length >=4)
                {
                    selectOptionsSqlStr += item._b + "." + item._a + " as "
                            + subCols[0] + ConstDataBase.V_AND
                            + subCols[1] + ConstDataBase.V_AND
                            + subCols[2] + ConstDataBase.V_AND+ vid + ",";
                }
                else {
                    selectOptionsSqlStr += item._b + "." + item._a + " as " + item._a + ConstDataBase.V_AND + vid + ",";
                }
            } else {
                System.out.println("DataBaseService getSelectOptionsForSqlQuery error parm:" + item);
                continue;
            }
        }
        if (STRING.isBlank(selectOptionsSqlStr)) {
            return null;
        }
        return selectOptionsSqlStr.substring(0, selectOptionsSqlStr.length() - 1);
    }

    public String getAllSelectOptionsStrForStore(String json, String vid) {
        List<Tuple<String, String>> list = getListforQueryOptions(json);
        List<String> listForStore = new ArrayList<>();
        for (Tuple<String, String> item : list) {
            if (item._b.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                listForStore.add(item._a + ConstDataBase.V_AND + item._b);
            } else if (item._b.startsWith(ConstDataBase.VIEW_HRAD_FLAG)) {
                listForStore.add(item._a);
            } else {
                System.out.println("DataBaseService getSelectOptionsForSqlQuery error parm:" + item);
                continue;
            }

        }
        return JSON.stringify(listForStore);
    }

    public boolean isHasRelation(String mainTable, String subTable) {
        String templateName = mainTable;
        List<Tuple<String,Object>> listRet = new ArrayList<>();
        Tuple<String,Object> tmp = null;
        List<String> listDataTable = new ArrayList<>();
        listDataTable.add(subTable);
        if (templateName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG))
        {
            mainTable = templateName;
            if (subTable.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG))
            {
                tmp = getObjRelationshipTable(mainTable, subTable);
            }
            else if (subTable.startsWith(ConstDataBase.VIEW_HRAD_FLAG))
            {
                tmp = getObjRelationshipTableWithView (mainTable, subTable);
            }
            else
            {
                return  false;
            }
            if (null == tmp)
            {
                return false;
            }
            else {

                return  true;
            }

        }
        else if (templateName.startsWith(ConstDataBase.VIEW_HRAD_FLAG))
        {
            listRet = getListRelationshipTableByViewTemplate(templateName,listDataTable);
        }
        else
        {
            return false;
        }

        if (null == listRet || listRet.size() == 0)
        {
            return false;
        }
        else {
            return  true;
        }
    }

    private Tuple<String, Object> getObjRelationshipTable(String mainTable, String subTable) {
        for (Map.Entry<String, Map<String, String>> entry : relationGroupMap.entrySet()) {
            String relationshipTableName = entry.getKey();
            boolean mainTableIsExist = false, subTableIsExist = false;
            String mainTableId = "", subTableId = "";
            for (Map.Entry<String, String> subentry : entry.getValue().entrySet()) {
                if (mainTable.equals(subentry.getKey())) {
                    mainTableIsExist = true;
                    mainTableId = subentry.getValue();
                }
                if (subTable.equals(subentry.getKey())) {
                    subTableIsExist = true;
                    subTableId = subentry.getValue();
                }
            }
            if (mainTableIsExist && subTableIsExist) {
                if (relationshipTableName.startsWith("r_")) {
                    return Tuple.t2(ConstDataBase.N_TO_N,
                            Tuple.t4(
                                    Tuple.t2(mainTable, "id"),
                                    Tuple.t2(relationshipTableName, mainTableId),
                                    Tuple.t2(subTableId, "id"),
                                    Tuple.t2(relationshipTableName, subTableId))
                    );
                } else {
                    return Tuple.t2(ConstDataBase.ONE_TO_N,
                            Tuple.t2(
                                    Tuple.t2(mainTable, mainTableId),
                                    Tuple.t2(subTable, subTableId)
                            )
                    );
                }
            } else {
                continue;
            }
        }
        return null;
    }

    private Tuple<String, Object> getObjRelationshipTableWithView(String mainTable, String viewId) {
        Tuple<String, Object> ret = null;
        /* 视图关系，只考虑与主表是否存在连接关系，副表不考虑 */
        String itemMainTable = "";
        ViewForGrid vfg = getViewForGridByVid(viewId);
        if (null == vfg) {
            return null;
        } else {
            itemMainTable = vfg.get("main_table");
        }
        ret = getObjRelationshipTable(mainTable, itemMainTable);
        if (null == ret) {
            return null;
        } else {
            List listSelectCols = JSON.parseArray(vfg.get("select_columns"));
            if (ret._a.equals(ConstDataBase.N_TO_N)) {
                Tuple.T4 tmp = (Tuple.T4) ret._b;
                Tuple<String, String> subTable = (Tuple<String, String>) tmp._c;
                String viewRealId = "";
                for (Object itemCol : listSelectCols)
                {
                    if ((itemCol.toString().indexOf(subTable._b + ConstDataBase.V_AND + subTable._a) ==0)
                        && (itemCol.toString().indexOf(viewId) >=0))
                    {
                        viewRealId = itemCol.toString();
                        break;
                    }
                }
                return Tuple.t2(ConstDataBase.N_TO_N,
                        Tuple.t4(tmp._a,
                                tmp._b,
                                Tuple.t2(viewId, viewRealId),
                                tmp._a)
                );
            } else if (ret._a.equals(ConstDataBase.ONE_TO_N)) {
                Tuple<Object, Object> tmp = (Tuple<Object, Object>) ret._b;
                Tuple<String, String> subTable = (Tuple<String, String>) tmp._b;
                String viewRealId = "";
                for (Object itemCol : listSelectCols)
                {
                    if ((itemCol.toString().indexOf(subTable._b + ConstDataBase.V_AND + subTable._a) ==0)
                            && (itemCol.toString().indexOf(viewId) >=0))
                    {
                        viewRealId = itemCol.toString();
                        break;
                    }
                }
                return Tuple.t2(ConstDataBase.ONE_TO_N,
                        Tuple.t2(tmp._a,
                                Tuple.t2(viewId, viewRealId)
                        )
                );
            } else {
                return null;
            }

        }
    }

    private List<Tuple<String,Object>>  getListRelationshipTableByViewTemplate(String templateName, List<String> listDataTable) {

        ViewForGrid vfg = getViewForGridByVid(templateName);
        List<Tuple<String,Object>> listRet = new ArrayList<>();
        if(null==vfg)
        {
            return null;
        }
        String  mainTable = vfg.get("main_table");
        List<String>  listViewSubTables = new ArrayList<>();
        listViewSubTables.add(mainTable);
        List<String>  listRelataDataTables = JSON.parseArray(vfg.get("relational_data_tables"));
        for (String item : listRelataDataTables)
        {
            if (!item.equals(mainTable))
            {
                listViewSubTables.add(item);
            }
        }

        for (String item : listDataTable)
        {
            Tuple<String,Object> tmp = null;
            if (item.equals(templateName))
            {
                continue;
            }
            for (int i =0; i <listViewSubTables.size(); i++) {
                mainTable = listViewSubTables.get(i);
                if (item.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                    tmp = getObjRelationshipTable(mainTable, item);
                } else if (item.startsWith(ConstDataBase.VIEW_HRAD_FLAG)) {
                    tmp = getObjRelationshipTableWithView(mainTable, item);
                } else {
                    continue;
                }
                if (null == tmp) {
                    continue;
                }
                else
                {
                    // 存在关系
                    break;
                }
            }
            if (null == tmp)
            {
                return null;
            }
            else{
                listRet.add(tmp);
            }
        }
        return listRet;
    }
    private List<Tuple<String,Object>> getListRelationshipTableForView(String json)
    {
        String templateName = getValueByKeyFromJson("template_name", json);
        String mainTable = "";
        List<String> listDataTable = JSON.parseArray(getValueByKeyFromJson("show_tables", json));
        List<String> newList = new  ArrayList<>();
        Set set = new  HashSet();
        for (String item: listDataTable) {
            if(set.add(item)){
                newList.add(item);
            }
        }

        List<Tuple<String,Object>> listRet = new ArrayList<>();
        Tuple<String,Object> tmp = null;
        if (templateName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG))
        {
            mainTable = templateName;
            for (String item : newList)
            {
                if (mainTable.equals(item))
                {
                    //repeat with maintable
                    continue;
                }
                if (item.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG))
                {
                    tmp = getObjRelationshipTable(mainTable, item);
                }
                else if (item.startsWith(ConstDataBase.VIEW_HRAD_FLAG))
                {
                    tmp = getObjRelationshipTableWithView (mainTable, item);
                }
                else
                {
                    System.out.println("err parm" + item);
                    return null;
                }

                if (null == tmp)
                {
                    return null;
                }
                else {
                    listRet.add(tmp);
                }

            }

        }
        else if (templateName.startsWith(ConstDataBase.VIEW_HRAD_FLAG))
        {
            listRet = getListRelationshipTableByViewTemplate(templateName,newList);
        }
        else
        {
            return null;
        }
        return listRet;
    }

    private String getJoinOfViewSql(String json)
    {
        List<Tuple<String,Object>>  list=  getListRelationshipTableForView(json);
        String templateName = getValueByKeyFromJson("template_name", json);
        String joinsql = "";

        if (templateName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG))
        {
            joinsql = templateName;
            for (Tuple<String,Object>  item : list)
            {
                if (item._a.equals(ConstDataBase.N_TO_N))
                {
                    Tuple.T4 tables = (Tuple.T4) item._b;
                    joinsql = "(" + joinsql + ")"
                            + " left join "
                            +  ((Tuple<String,String>)tables._b)._a
                            + " on ("
                            +  ((Tuple<String,String>)tables._a)._a + "." + ((Tuple<String,String>)tables._a)._b
                            + "="
                            + ((Tuple<String,String>)tables._b)._a  + "." + ((Tuple<String,String>)tables._b)._b + ")";
                    joinsql = "(" + joinsql + ")" +
                            " left join "
                            + ((Tuple<String,String>)tables._c)._a
                            + " on "
                            + ((Tuple<String,String>)tables._c)._a + "." + ((Tuple<String,String>)tables._c)._b
                            + "="
                            + ((Tuple<String,String>)tables._d)._a + "." + ((Tuple<String,String>)tables._d)._b;

                }
                if (item._a.equals(ConstDataBase.ONE_TO_N))
                {
                    Tuple<Object, Object> tables = (Tuple<Object, Object>) item._b;
                    joinsql = "(" + joinsql + ")"
                            + " left join "
                            + ((Tuple<String,String>)tables._b)._a
                            + " on ("
                            + ((Tuple<String,String>)tables._a)._a + "." +  ((Tuple<String,String>)tables._a)._b
                            + "="
                            + ((Tuple<String,String>)tables._b)._a + "." + ((Tuple<String,String>)tables._b)._b
                            + ")";//subTableOption
                }
            }
        }
        else if (templateName.startsWith(ConstDataBase.VIEW_HRAD_FLAG))
        {
            ViewForGrid vfg = getViewForGridByVid(templateName);
            if (null == vfg)
            {
                return joinsql;
            }
            joinsql = templateName;
            List listCols =JSON.parseArray(vfg.get("select_columns"));
            for (Tuple<String,Object>  item : list)
            {
                if (item._a.equals(ConstDataBase.N_TO_N))
                {
                    Tuple.T4 tables = (Tuple.T4) item._b;
                    String templateRealId = "";
                    for (Object subCol : listCols)
                    {
                        //必须以 ID+ __ + table 开始，不然会找错列名
                        if ((subCol.toString().indexOf(((Tuple<String,String>)tables._a)._b+ ConstDataBase.V_AND + ((Tuple<String,String>)tables._a)._a ) ==0)
                            && (subCol.toString().indexOf(templateName)>=0)){
                            templateRealId = subCol.toString();
                        }

                    }
                    joinsql = "(" + joinsql + ")"
                            + " left join "
                            +  ((Tuple<String,String>)tables._b)._a
                            + " on ("
                            + templateName + "." + templateRealId
                            + "="
                            + ((Tuple<String,String>)tables._b)._a  + "." + ((Tuple<String,String>)tables._b)._b + ")";
                    joinsql = "(" + joinsql + ")" +
                            " left join "
                            + ((Tuple<String,String>)tables._c)._a
                            + " on "
                            + ((Tuple<String,String>)tables._c)._a + "." + ((Tuple<String,String>)tables._c)._b
                            + "="
                            + ((Tuple<String,String>)tables._d)._a + "." + ((Tuple<String,String>)tables._d)._b;

                }
                if (item._a.equals(ConstDataBase.ONE_TO_N))
                {
                    Tuple<Object, Object> tables = (Tuple<Object, Object>) (item._b);
                    String templateRealId = "";
                    for (Object subCol : listCols)
                    {
                        //必须以 ID+ __ + table 开始，不然会找错列名
                        if ((subCol.toString().indexOf(((Tuple<String,String>)(tables._a))._b+ ConstDataBase.V_AND + ((Tuple<String,String>)(tables._a))._a) ==0)
                                && (subCol.toString().indexOf(templateName)>=0)){
                            templateRealId = subCol.toString();
                        }

                    }
                    joinsql = "(" + joinsql + ")"
                            + " left join "
                            + ((Tuple<String,String>)(tables._b))._a
                            + " on ("
                            + templateName + "." + templateRealId
                            + "="
                            + ((Tuple<String,String>)(tables._b))._a + "." + ((Tuple<String,String>)(tables._b))._b
                            + ")";//subTableOption
                }
            }
        }
        return joinsql;
    }
    public String getShowViewReportSql(String json)
    {
        if (STRING.notBlank(json)) {
            String vid = getValueByKeyFromJson("vid", json);
            String queryOpts = getSelectOptionsForSqlQuery(json,vid);
            String joinSql = getJoinOfViewSql(json);
            String whereSql = getWhereOfViewSql(json);
            if (STRING.isBlank(queryOpts)
                    || STRING.isBlank(joinSql)
                    || STRING.isBlank(whereSql)
                    )
            {
                return null;
            }
            return  " SELECT "
                    + queryOpts
                    + " from "
                    + joinSql
                    + whereSql
                    + ";";
        }
        else {
            return null;
        }
    }


    public String getCreateViewSql(String json)
    {
        if (STRING.notBlank(json)) {
            String vid = getValueByKeyFromJson("vid", json);
            String queryOpts = getSelectOptionsForSqlQuery(json,vid);
            String joinSql = getJoinOfViewSql(json);
            String whereSql = getWhereOfViewSql(json);
            if (STRING.isBlank(queryOpts)
                    || STRING.isBlank(joinSql)
                    || STRING.isBlank(whereSql)
                    )
            {
                return null;
            }
            return  " CREATE VIEW "
                    + vid
                    + " AS SELECT "
                    + queryOpts
                    + " from "
                    + joinSql
                    + whereSql
                    + ";";
        }
        else {
            return null;
        }
    }


/*
[{"name":"t_role","value":[{"name": "role_level", "value":4 },{"name": "role_name", "value":12}]},
{"name":"t_user","value":[{"name":"name", "value" : 12},{"name":"sex", "value" : 12}]}]
*/
    public List<String> getColumnsListOfDataTable(String dataTableName)
    {
        if (STRING.isBlank(dataTableName))
        {
            return null;
        }
        List<String> listCols = new ArrayList<>();
        Map<String, Map<String, Integer>> mydb = this.db.getDbStructures();
        for(Map.Entry<String, Map<String, Integer>> entry : mydb.entrySet()) {
            String tableName = entry.getKey();
            System.out.println(tableName);
            if (tableName.equals(dataTableName)) {
                Map<String, Integer> table = entry.getValue();
                List <Map<String,Object>> listColsOfTable = new ArrayList<>();
                for (Map.Entry<String, Integer> subentry : table.entrySet()) {
                    listCols.add(subentry.getKey());
                    System.out.println(subentry.getKey());
                }
            }
        }
        return listCols;
    }

    public ViewForGrid getViewForGridByVid(String vid){
        List<ViewForGrid> listRet = this.db.get(t -> t.query(ViewForGrid.class, "select * from view_for_grid where vid ='" + vid + "';"));
        if (listRet.size() == 1) {
            return listRet.get(0);
        }
        else {
            return null;
        }
    }
    public List<String> getColumnsListWithOutVidOfView(String viewId)
    {
        ViewForGrid vfg = getViewForGridByVid(viewId);
        if (null != vfg){
            return (List<String>)JSON.parseArray(vfg.get("select_columns"));
        }
        else
        {
            return null;
        }
    }
    public List<Tuple<String, String>> getColumnsListOfView(String viewId)
    {
        List<Tuple<String, String>> listForColsView = new ArrayList<>();
        ViewForGrid vfg = getViewForGridByVid(viewId);
        if (null != vfg){
            List<String> listForView = JSON.parseArray(vfg.get("select_columns"));
            for (String item : listForView)
            {
                listForColsView.add(Tuple.pair(item,viewId));
            }
            return listForColsView;
        }
        else
        {
            return null;
        }
    }


    public List<Map<String,Object>> getDataTableList()
    {
        List<Map<String,Object>> listDataTable = new ArrayList<>();
        for(Map.Entry<String, List<Tuple<String,Integer>>> entry : databaseMap.entrySet()) {
            String tableName = entry.getKey();
            Map<String,Object> mapTableItem  = new HashMap<>();
            if (tableName.startsWith(ConstDataBase.DATA_TABLE_HEAD_FLAG)) {
                List<Tuple<String,Integer>> table = entry.getValue();
                List <Map<String,Object>> listColsOfTable = new ArrayList<>();
                for (Tuple<String,Integer> subItem : table) {
                    Map<String,Object> mapSubItem = new HashMap<>();
                    if ("id".equals(subItem._a) || subItem._a.endsWith("_id"))
                    {
                        continue;
                    }
                    mapSubItem.put("key", subItem._a);
                    mapSubItem.put("value",subItem._b);
                    listColsOfTable.add(mapSubItem);
                }

                mapTableItem.put("key", tableName);
                mapTableItem.put("value",listColsOfTable);
                listDataTable.add(mapTableItem);
            }
        }
        return listDataTable;
    }

}
